﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using ConnectLib.DAO;
using MSSQLProvider.DataBase;

namespace ConnectLib.DAL
{
    public class ServiceDal : MSSQLDataAccess
    {
        private static ServiceDal _instance = new ServiceDal();

        public static ServiceDal Instance
        {
            get
            {
                return _instance;
            }
        }

        private bool Exist(string serviceid)
        {
            if (
                ExecuteScalar(string.Format("SELECT [ServiceId] FROM [Services] WHERE [ServiceId] = '{0}'", serviceid)) ==
                null)
            {
                return false;
            }

            return true;
        }

        public int AddService(ServiceDao service)
        {
            try
            {
                if (!Exist(service.ServiceId))
                {
                    List<string> arrFielName = new List<string>();
                    List<string> arrParamName = new List<string>();
                    List<object> arrValue = new List<object>();

                    arrFielName.Add("ServiceId");
                    arrParamName.Add("@ServiceId");
                    arrValue.Add(service.ServiceId);

                    arrFielName.Add("Name");
                    arrParamName.Add("@Name");
                    arrValue.Add(service.Name);

                    arrFielName.Add("Price");
                    arrParamName.Add("@Price");
                    arrValue.Add(service.Price);

                    arrFielName.Add("ImageLink");
                    arrParamName.Add("@ImageLink");
                    arrValue.Add(service.ImageLink);

                    arrFielName.Add("Summary");
                    arrParamName.Add("@Summary");
                    arrValue.Add(service.Summary);

                    arrFielName.Add("FullContent");
                    arrParamName.Add("@FullContent");
                    arrValue.Add(service.FullContent);

                    arrFielName.Add("Active");
                    arrParamName.Add("@Active");
                    arrValue.Add(service.Active);

                    arrFielName.Add("Sort");
                    arrParamName.Add("@Sort");
                    arrValue.Add(service.Sort);

                    arrFielName.Add("HighSort");
                    arrParamName.Add("@HighSort");
                    arrValue.Add(service.HighSort);

                    var queryBuider = new StringBuilder();
                    queryBuider.Append("INSERT INTO [Services] (");
                    int i = 0;

                    for (; i < arrFielName.Count; i++)
                    {
                        queryBuider.Append(arrFielName[i]);
                        if (i < arrFielName.Count - 1)
                            queryBuider.Append(",");
                    }
                    queryBuider.Append(")");
                    queryBuider.Append(" VALUES (");
                    i = 0;

                    for (; i < arrParamName.Count; i++)
                    {
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrParamName.Count - 1)
                            queryBuider.Append(",");
                    }
                    queryBuider.Append(")");

                    return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
                }
                
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return 0;
        }

        public int UpdateService(ServiceDao service)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("Name");
                arrParamName.Add("@Name");
                arrValue.Add(service.Name);

                arrFielName.Add("Price");
                arrParamName.Add("@Price");
                arrValue.Add(service.Price);

                arrFielName.Add("ImageLink");
                arrParamName.Add("@ImageLink");
                arrValue.Add(service.ImageLink);

                arrFielName.Add("Summary");
                arrParamName.Add("@Summary");
                arrValue.Add(service.Summary);

                arrFielName.Add("FullContent");
                arrParamName.Add("@FullContent");
                arrValue.Add(service.FullContent);

                arrFielName.Add("Active");
                arrParamName.Add("@Active");
                arrValue.Add(service.Active);

                arrFielName.Add("Sort");
                arrParamName.Add("@Sort");
                arrValue.Add(service.Sort);

                arrFielName.Add("HighSort");
                arrParamName.Add("@HighSort");
                arrValue.Add(service.HighSort);

                arrFielName.Add("ServiceId");
                arrParamName.Add("@ServiceId");
                arrValue.Add(service.ServiceId);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Services] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteService(string serviceid)
        {
            return ExecuteNonQuery(string.Format("DELETE FROM [Services] WHERE [ServiceId] = {0}", serviceid));
        }

        public string DeleteServices(string[] services)
        {
            string ids = string.Empty;

            foreach (string service in services)
            {
                if (!ExecuteNonQuery(string.Format("DELETE FROM [Services] WHERE [ServiceId] = '{0}'", service)))
                {
                    ids += service + ',';
                }
            }

            if (ids.Length > 0)
            {
                ids = ids.Remove(ids.Length - 1, 1);
            }

            return ids;
        }
        
        public string GetDefaultServiceName()
        {
            string sName = string.Empty;
            SqlDataReader dr = null;

            try
            {
                dr =
                    ExecuteReader("SELECT TOP 1 [Name] FROM [Services] WITH(NOLOCK)");
                while (dr.Read())
                {
                    sName = dr[0].ToString();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }

            return sName;
        }

        public string GetServiceName(string serviceid)
        {
            string sName = string.Empty;
            SqlDataReader dr = null;

            try
            {
                dr =
                    ExecuteReader(string.Format("SELECT TOP 1 [Name] FROM [Services] WITH(NOLOCK) WHERE [ServiceId] = '{0}'",
                        serviceid));
                while (dr.Read())
                {
                    sName = dr[0].ToString();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }

            return sName;
        }

        public ServiceDao GetService(string serviceid)
        {
            ServiceDao service = null;

            try
            {
                Hashtable hstb = ExecuteHashtable(string.Format("SELECT * FROM [Services] WITH(NOLOCK) WHERE ServiceId = '{0}'", serviceid));

                if(hstb != null)
                {
                    service = new ServiceDao(hstb);
                }
            }
            catch (Exception)
            {
                throw;
            }

            return service;
        }

        public string GetServiceId(string servicename)
        {
            string serviceid = "";
            SqlDataReader dr = null;

            try
            {
                dr =
                    ExecuteReader(
                        string.Format("SELECT TOP 1 [ServiceId] FROM [Services] WITH(NOLOCK) WHERE [Name] = '{0}'",
                            servicename));

                while (dr.Read())
                {
                    serviceid = dr[0].ToString();
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if(dr != null)
                    dr.Close();
            }

            return serviceid;
        }

        public List<ServiceDao> GetServices()
        {
            List<Hashtable> lsHtbl = null;
            List<ServiceDao> services = null;
            try
            {
                lsHtbl = ExecuteArrayHastable("SELECT * FROM [Services] WITH(NOLOCK)");
                services = new List<ServiceDao>(lsHtbl.Count);
                foreach (Hashtable hashtable in lsHtbl)
                {
                    services.Add(new ServiceDao(hashtable));
                }
            }
            catch (Exception)
            {
                
                throw;
            }

            return services;
        }

        public List<ServiceDao> GetActiveServices()
        {
            List<Hashtable> lsHtbl = null;
            List<ServiceDao> services = null;
            try
            {
                lsHtbl = ExecuteArrayHastable("SELECT * FROM [Services] WITH(NOLOCK) WHERE [Active] = 1 ORDER BY [Sort] ASC");
                services = new List<ServiceDao>(lsHtbl.Count);
                foreach (Hashtable hashtable in lsHtbl)
                {
                    services.Add(new ServiceDao(hashtable));
                }
            }
            catch (Exception)
            {

                throw;
            }

            return services;
        }

        public List<ServiceDao> GetHighServices()
        {
            List<Hashtable> lsHtbl = null;
            List<ServiceDao> services = null;
            try
            {
                lsHtbl = ExecuteArrayHastable("SELECT * FROM [Services] WITH(NOLOCK) WHERE [Active] = 1 AND [HighSort] > 0 ORDER BY [HighSort] ASC");
                services = new List<ServiceDao>(lsHtbl.Count);
                foreach (Hashtable hashtable in lsHtbl)
                {
                    services.Add(new ServiceDao(hashtable));
                }
            }
            catch (Exception)
            {

                throw;
            }

            return services;
        }
    }
}
